Example analysis of mysql slow query operation [open test confirm etc.]
- 2021-12-19 07:07:34
- OfStack
This article illustrates the mysql slow query operation. Share it for your reference, as follows:
mysql Some sql executes slowly, potentially causing server load to soar
First, the query determines that the load is affected by mysql, using top command, ps command, and so on
Secondly, enter MySQL, use show full processlist to query the sql statement in execution, look at the problem, and use explain command to view the status
Finally, find out whether the sql statement kills or optimizes
mariadb service installed on centos7
yum -y install mariadb-server mariadb-devel
Open slow query
more /etc/my.cnf.d/server.cnf
[mariadb]
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/slow.log
long_query_time=1
Start the mariadb service
systemctl start mariadb
Query whether the slow query of mysql is turned on, and how long it is a slow query
MariaDB [(none)]> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
# If you don't open slow query, you can open it on the command line
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
Test slow queries and view logs
MariaDB [(none)]> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
[root@localhost ~]# more /usr/local/mysql/data/slow.log
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 180930 23:51:07
# User@Host: root[root] @ localhost []
# Thread_id: 2 Schema: QC_hit: No
# Query_time: 2.001017 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1538322667;
select sleep(2);
Confirm slow query
MariaDB [(none)]> show full processlist; # View state Slow query in progress
+----+------+-----------+------+---------+------+------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+------------+-----------------------+----------+
| 3 | root | localhost | NULL | Query | 9 | User sleep | select sleep(10) | 0.000 |
| 4 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 |
+----+------+-----------+------+---------+------+------------+-----------------------+----------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show full processlist; # View state The slow query has ended, but the user has logged in
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
| 3 | root | localhost | NULL | Sleep | 1 | | NULL | 0.000 |
| 4 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
2 rows in set (0.00 sec)
For more readers interested in MySQL related content, please check the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia" and "MySQL Database Lock Related Skills Summary"
I hope this article is helpful to everyone's MySQL database.